﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using ManualMappingentities;

namespace ManualMappingDAL
{
    public class CustomersDataAccess
    {
        public List<Customer> GetCustomersByCountry(string country)
        {
            List<Customer> customers = new List<Customer>();
            using (SqlConnection conn = new SqlConnection(@"Server=.\sqlexpress;DataBase=Northwind;Integrated Security=SSPI"))
            {
                conn.Open();
                SqlCommand cmd = new SqlCommand("select * from Customers where country = @country", conn);
                cmd.Parameters.Add(new SqlParameter("@country", country));
                using(IDataReader dr = cmd.ExecuteReader())
                {
                    while(dr.Read())
                    {
                        Customer cust = new Customer();
                        cust.CustomerID = dr.GetString(0);
                        cust.CompanyName = dr.GetString(1);
                        cust.ContactName = dr.GetString(2);
                        cust.Address = dr.GetString(4);
                        //cust.Region = dr.GetString(6);
                        cust.Country = dr.GetString(8);
                        customers.Add(cust);
                    }
                }
            }
            return customers;
        }
    }
}
